package com.yc.vote.mapper;

import com.yc.vote.entity.Items;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author lyl
 * @since 2022-11-06
 */
public interface ItemsMapper extends BaseMapper<Items> {

    @Insert("insert into items values(default,#{iname},#{vid})")
    @Options(useGeneratedKeys = true, keyColumn = "id", keyProperty = "id")
    int insert(Items items);

    @Select("select \n" +
            " a.*, b.commitSum, c.itemCount\n" +
            "from items a \n" +
            "left join (select iid, count(*) commitSum from record where vid = #{vid} group by iid ) b\n" +
            "   on a.id = b.iid\n" +
            "left join (select vid, count(*) itemCount from record where vid = #{vid} group by vid) c\n" +
            "   on a.vid = c.vid\n" +
            "where a.vid = #{vid}")
    List<Items> selectByVid(int vid);
}
